package studentManagementSystem;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;

public class Util {
    /*
     * 工具中的构造方法一般都是私有的。
     * 因为工具类中的方法都是静态的，不需要new对象，直接用类名调用
     * */
    private Util() {
    }

    /*
     * 注册驱动只需要注册一次就可以了，不能调用一次方法注册一次
     * 所以用静态代码块，静态代码块在类加载时执行，并且只执行一次
     * */
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    /*
     * 获取数据库连接对象
     * @return连接对象
     * @throws SQLException
     * */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc?serverTimezone=UTC", "root", "123456");
    }

    /*
     * 关闭资源
     * @param conn连接对象
     * @param ps 数据库操作对象
     * @param rs 结果集
     * */
    public static void close(Connection conn, Statement ps, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /*
     * 初始化用户界面
     * */
    public static void userUI() {
        System.out.println("--------  欢迎使用学生管理系统，请认真阅读以下使用说明  --------");
        System.out.println("-----------------  功能编号对应的功能：  ----------------" + "\n" +
                "1.表示查看所有学生信息。" + "\n" +
                "2.表示添加学生信息。" + "\n" +
                "3.表示删除学生信息。" + "\n" +
                "4.表示修改学生信息。" + "\n" +
                "5.表示查找学生信息。 " + "\n" +
                "6.表示按照性别查找学生信息 " + "\n" +
                "7.表示查找成绩排前n名学生 " + "\n" +
                "8.表示查找成绩大于60分或小于60的同学 " + "\n" +
                "9.计算所有学生平均成绩 " + "\n" +
                "10.计算所有男学生或女学生平均成绩 " + "\n" +
                "功能完善中······ " + "\n" +
                "0.表示退出系统");
    }

    /*
     * 获取用户输入的编号
     * */
    public static int getNum() {
        Scanner s = new Scanner(System.in);
        System.out.println("请输入编号：");
        int num = Integer.parseInt(s.nextLine());
        return num;
    }

    /*
     * 判断用户输入功能号
     * */
    public static void judgeNum() {
        while (true) {
            int num = getNum();
            switch (num) {
                case 1:
                    selectAll();
                    break;
                case 2:
                    increase();
                    break;
                case 3:
                    decrease();
                    break;
                case 4:
                    modify();
                    break;
                case 5:
                    select();
                    break;
                case 6:
                    selectSex();
                    break;
                case 7:
                    rank();
                    break;
                case 8:
                    pass();
                    break;
                case 9:
                    System.out.println("所有学生平均成绩为" + String.format("%.2f", avg()));
                    break;
                case 10:
                    System.out.println("所有学生平均成绩为" + String.format("%.2f", sexAvg()));
                    break;
                case 0:
                    return;
            }
        }


    }


    /*
     * 功能号1：查询所有学生信息
     * */
    public static void selectAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
//          获取连接
            conn = Util.getConnection();
//            获取数据库操作对象
            stmt = conn.createStatement();
//            执行sql语句
            String sql = "select * from stu";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String sno = rs.getString("sno");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String score = rs.getString("score");
                String sex = rs.getString("sex");
                System.out.println(sno + "  " + name + "  " + age + "  " + score + "  " + sex);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, stmt, rs);
        }
    }


    /*
     * 功能号2：添加学生信息
     * */
    public static void increase() {
        Connection conn = null;
        PreparedStatement ps = null;
        //            获得输入数据
        Scanner s = new Scanner(System.in);
        System.out.println("请输入需要添加的学生信息，务必按照姓名、年龄、分数、学号、性别、学院的顺序依次输入，并以”，“分隔。");
        Scanner sc = new Scanner(System.in);
        String studentInformation = sc.next();
        String[] si = studentInformation.split("，");
        String name = si[0];
        String age = si[1];
        String score = si[2];
        String sno = si[3];
        String sex = si[4];
        String academy = si[5];
        try {
            conn = Util.getConnection();
            String sql = "insert into stu(name,age,score,sno,sex,academy) values (?, ?, ?, ?, ?, ?)";
            ps = conn.prepareStatement(sql);
//            将数据插入数据库
            ps.setString(1, name);
            ps.setString(2, age);
            ps.setString(3, score);
            ps.setString(4, sno);
            ps.setString(5, sex);
            ps.setString(6, academy);
            int count = ps.executeUpdate();
            if (count == 1) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("已经存在该学生信息，请重新插入！");
            increase();
        } finally {
            Util.close(conn, ps, null);
        }
    }

    /*
     * 功能号3：删除学生信息
     * */
    public static void decrease() {
        Connection conn = null;
        PreparedStatement ps = null;
        System.out.println("请输入需要删除学生的学号或姓名：");
        Scanner sc = new Scanner(System.in);
        String deletSnoName = sc.next();
        try {
            conn = Util.getConnection();
            String sql = "delete from stu where sno = ? or name = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, deletSnoName);
            ps.setString(2, deletSnoName);
            int count = ps.executeUpdate();
            if (count == 1) {
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("删除失败，请检查输入数据的有效性后再次删除！");
            decrease();
        } finally {
            Util.close(conn, ps, null);
        }
    }


    /*
     * 功能号4：修改学生信息
     * */
    public static void modify() {
        Connection conn = null;
        PreparedStatement ps = null;

        System.out.println("请输入需要修改学生的学号：");
        Scanner sc = new Scanner(System.in);
        String modifySno = sc.nextLine();
        System.out.println("请输入需要修改的字段和内容，以”，“号间隔。");
        String mofifyContents = sc.nextLine();
        String[] si = mofifyContents.split("，");
        String columName = si[0];
        String content = si[1];
//        判断用户输入字段
        if (columName.equals("姓名")) {
            columName = "name";
        } else if (columName.equals("年龄")) {
            columName = "age";
        } else if (columName.equals("成绩")) {
            columName = "score";
        } else if (columName.equals("性别")) {
            columName = "sex";
        } else {
            System.out.println("输入字段有误，请重新输入！");
        }
        try {
            conn = Util.getConnection();
            String sql = "update stu set " + columName + " = ?  where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, content);
            ps.setString(2, modifySno);
            int count = ps.executeUpdate();
            if (count == 1) {
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println(modifySno + columName + content);
            System.out.println("修改失败，请检查输入数据的有效性后再次修改！");
            modify();
        } finally {
            Util.close(conn, ps, null);
        }
    }

    /*
     * 功能号5：表示查找学生信息
     * */
    public static void select() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查找的学生学号：");
        String selectSno = sc.next();
        try {
            conn = Util.getConnection();
            String sql = "select * from stu where sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, selectSno);
            rs = ps.executeQuery();
            while (rs.next()) {
                String sno = rs.getString("sno");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String score = rs.getString("score");
                String sex = rs.getString("sex");
                System.out.println(sno + "  " + name + "  " + age + "  " + score + "  " + sex);
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("没有查到该学生信息！请检查学号是否正确");
            select();
        } finally {
            Util.close(conn, ps, rs);
        }
    }

    /*
     * 6.表示按照性别查找学生信息
     * */
    public static void selectSex() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查找的学生的性别：");
        String selectSex = sc.next();
        try {
            conn = Util.getConnection();
            String sql = "select * from stu where sex = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, selectSex);
            rs = ps.executeQuery();
            while (rs.next()) {
                String sno = rs.getString("sno");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String score = rs.getString("score");
                String sex = rs.getString("sex");
                String academy = rs.getString("academy");
                System.out.println(sno + "  " + name + "  " + age + "  " + score + "  " + sex + " " + academy);
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("查找失败，请检查输入数据的有效性后再次查找");
            selectSex();
        } finally {
            Util.close(conn, ps, rs);
        }
    }

    /*
     * 7.表示查找成绩排前n名学生
     * */
    public static void rank() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("查找前n名同学，请输入n：");
        String selectrank = sc.next();
        try {
            conn = Util.getConnection();
            String sql = "select * from stu order by score desc limit ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, selectrank);
            rs = ps.executeQuery();
            while (rs.next()) {
                String sno = rs.getString("sno");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String score = rs.getString("score");
                String sex = rs.getString("sex");
                String academy = rs.getString("academy");
                System.out.println(sno + "  " + name + "  " + age + "  " + score + "  " + sex + " " + academy);
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("查找失败，请检查输入数据的有效性后再次查找");
            selectSex();
        } finally {
            Util.close(conn, ps, rs);
        }
    }


    /*
     * 8.表示查找成绩大于60或小于60的同学
     * */
    public static void pass() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("请问您想查找大于60还是小于60的同学？大于60请输入1，小于60请输入0");
        String n = sc.next();
        try {
            conn = Util.getConnection();
            if (n.equals("1")) {
                stmt = conn.createStatement();
                String sql = "select * from stu where score > 60";
                rs = stmt.executeQuery(sql);
            } else {
                stmt = conn.createStatement();
                String sql = "select * from stu where score < 60";
                rs = stmt.executeQuery(sql);
            }
            while (rs.next()) {
                String sno = rs.getString("sno");
                String name = rs.getString("name");
                String age = rs.getString("age");
                String score = rs.getString("score");
                String sex = rs.getString("sex");
                String academy = rs.getString("academy");
                System.out.println(sno + "  " + name + "  " + age + "  " + score + "  " + sex + " " + academy);
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("查找失败，请检查输入数据的有效性后再次查找");
            selectSex();
        } finally {
            Util.close(conn, stmt, rs);
        }
    }

    /*
     * 9.计算所有学生平均成绩
     * */
    public static double avg() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        double avg = 0;
        try {
            conn = Util.getConnection();
            stmt = conn.createStatement();
            String sql = "select avg(score) from stu";
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                avg = rs.getDouble(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            Util.close(conn, stmt, rs);
        }
        return avg;
    }

    /*
     * 10.计算男生或者女生的平均成绩
     * */

    public static double sexAvg() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入需要查找的学生的性别：");
        String selectSex = sc.next();
        double sexAvg = 0;
        try {
            conn = Util.getConnection();
            String sql = "select avg(score) from stu where sex = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, selectSex);
            rs = ps.executeQuery();
            if (rs.next()) {
                sexAvg = rs.getDouble(1);
            }
        } catch (SQLException e) {
//            e.printStackTrace();
            System.out.println("查找失败，请检查输入数据的有效性后再次查找");
            sexAvg();
        } finally {
            Util.close(conn, ps, rs);
        }
        return sexAvg;
    }

}
